create table persona
(
	id_persona int not null,
	nombre varchar(100) not null,
	apellido varchar(100) not null,
	dni int not null,
	fecha_nacimiento varchar(20) not null
)
GO
/****** Object:  Index [PK_usuario]    Script Date: 11/21/2013 15:54:15 ******/
ALTER TABLE persona ADD  CONSTRAINT [PK_persona] PRIMARY KEY CLUSTERED 
(
	id_persona ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

create table cliente
(
	id_cliente int not null,
	id_persona int not null,
	poliza varchar(50) not null,
	estado bit
)
GO
ALTER TABLE cliente ADD  CONSTRAINT [PK_cliente] PRIMARY KEY CLUSTERED 
(
	id_cliente ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

ALTER TABLE cliente  WITH CHECK ADD  CONSTRAINT [FK_cliente_idpersona] FOREIGN KEY([id_persona])
REFERENCES persona ([id_persona])
GO

ALTER TABLE cliente CHECK CONSTRAINT [FK_cliente_idpersona]
GO

create table contraparte
(
	id_contraparte int not null,
	id_persona int not null
	)
GO
ALTER TABLE contraparte ADD  CONSTRAINT [PK_contraparte] PRIMARY KEY CLUSTERED 
(
	id_contraparte ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
ALTER TABLE contraparte  WITH CHECK ADD  CONSTRAINT [FK_contraparte_idpersona] FOREIGN KEY(id_persona)
REFERENCES persona (id_persona)
GO

ALTER TABLE contraparte CHECK CONSTRAINT [FK_contraparte_idpersona]
GO

create table asegurador
(
	id_asegurador int not null,
	cuit varchar(50) not null,
)
GO
ALTER TABLE asegurador ADD  CONSTRAINT [PK_asegurador] PRIMARY KEY CLUSTERED 
(
	id_asegurador ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


create table abogadoAseguradora
(
	id_abogadoAseguradora int not null,
	id_persona int not null,
	id_asegurador int not null
)
go
ALTER TABLE abogadoAseguradora ADD  CONSTRAINT [PK_abogadoAseguradora] PRIMARY KEY CLUSTERED 
(
	id_abogadoAseguradora ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
ALTER TABLE abogadoAseguradora  WITH CHECK ADD  CONSTRAINT [FK_abogadoAseguradora_idpersona] FOREIGN KEY(id_persona)
REFERENCES persona (id_persona)
GO

ALTER TABLE abogadoAseguradora CHECK CONSTRAINT [FK_abogadoAseguradora_idpersona]
GO	
ALTER TABLE abogadoAseguradora  WITH CHECK ADD  CONSTRAINT [FK_abogadoAseguradora_idasegurador] FOREIGN KEY(id_asegurador)
REFERENCES asegurador (id_asegurador)
GO

ALTER TABLE abogadoAseguradora CHECK CONSTRAINT [FK_abogadoAseguradora_idasegurador]
GO	

create table abogadoEstudio
(
	id_abogadoEstudio int not null,
	id_usuario int not null,
	dni int,
	fecha_nacimiento varchar(20)
)
go
ALTER TABLE abogadoEstudio ADD  CONSTRAINT [PK_abogadoEstudio] PRIMARY KEY CLUSTERED 
(
	id_abogadoEstudio ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
ALTER TABLE abogadoEstudio  WITH CHECK ADD  CONSTRAINT [FK_abogadoEstudio_idusuario] FOREIGN KEY(id_usuario)
REFERENCES usuario (id_usuario)
GO

ALTER TABLE abogadoEstudio CHECK CONSTRAINT [FK_abogadoEstudio_idusuario]
GO	

create table expediente
(
	id_expediente int not null,
	numero_expediente int not null,
	rubro varchar(100),
	siniestro varchar(200),
	id_cliente int not null,
	id_contraparte int not null,
	id_abogadoEstudio int not null,
	id_abogadoAseguradora int,
	polizaCliente varchar(200),
	polizaContraparte varchar(200)
)
go
ALTER TABLE expediente ADD  CONSTRAINT [PK_expediente] PRIMARY KEY CLUSTERED 
(
	id_expediente ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
ALTER TABLE expediente  WITH CHECK ADD  CONSTRAINT [FK_expediente_idcliente] FOREIGN KEY(id_cliente)
REFERENCES cliente (id_cliente)
GO
ALTER TABLE expediente CHECK CONSTRAINT [FK_expediente_idcliente]
GO	

ALTER TABLE expediente  WITH CHECK ADD  CONSTRAINT [FK_expediente_idcontraparte] FOREIGN KEY(id_contraparte)
REFERENCES contraparte (id_contraparte)
GO
ALTER TABLE expediente CHECK CONSTRAINT [FK_expediente_idcontraparte]
GO

ALTER TABLE expediente  WITH CHECK ADD  CONSTRAINT [FK_expediente_idabogadoEstudio] FOREIGN KEY(id_abogadoEstudio)
REFERENCES abogadoEstudio (id_abogadoEstudio)
GO
ALTER TABLE expediente CHECK CONSTRAINT [FK_expediente_idabogadoEstudio]
GO

ALTER TABLE expediente  WITH CHECK ADD  CONSTRAINT [FK_expediente_idabogadoAseguradora] FOREIGN KEY(id_abogadoAseguradora)
REFERENCES abogadoAseguradora (id_abogadoAseguradora)
GO
ALTER TABLE expediente CHECK CONSTRAINT [FK_expediente_idabogadoAseguradora]
GO

create table nota
(
	id_nota int not null,
	id_expediente int not null
	)
go
ALTER TABLE nota ADD  CONSTRAINT [PK_nota] PRIMARY KEY CLUSTERED 
(
	id_nota ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
ALTER TABLE nota  WITH CHECK ADD  CONSTRAINT [FK_nota_idexpediente] FOREIGN KEY(id_expediente)
REFERENCES expediente (id_expediente)
GO

ALTER TABLE nota CHECK CONSTRAINT [FK_nota_idexpediente]
GO
create table telefono
(
	id_telefono int not null,
	numero varchar(20) not null,
	id_objeto int not null,
	tipo_objeto int not null
)
GO
ALTER TABLE telefono ADD  CONSTRAINT [PK_telefono] PRIMARY KEY CLUSTERED 
(
	id_telefono ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
alter table nota
add Texto varchar(max) not null
GO
alter table nota
add Descripcion varchar(255) not null
GO
alter table asegurador
add razon_social varchar(200) not null
GO